<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Sync_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}

	function call_test(){
		$data = array(
		  ':p_invoice_number'  => '20151289',
		  ':p_invoice_total'   => '2015',
		  ':p_unpaid_amt'      => '1523',
		  ':p_pay_amount'      => '346234',
		  ':p_invoice_balance' => '246234',
		  ':p_created_by'      => '1',
		  ':p_updated_by'      => '2',
		  ':p_last_ins'        => '0',
		  ':p_account_num'     => 47,
		  ':p_receipt_number'  => 20150058,
		  ':p_receipt_date'    => '2014-12-23',
		  ':p_payment_type'    => 'CASH',
		  ':p_remarks'         => 'LKAJSLDJFASD',
		  ':p_reference'       => 'ASLDKJFWe'
		);
		$query = $this->db->query('call payments_migration(?,?,?,?,?,?,?,?,?,?,?,?,?,?,@pid,@err,@msg)',$data);
		$que = $this->db->query("SELECT @pid as lid, @err as err,@msg as msg");
        $msg; $err; $lid;
        foreach($que->result() as $keys){
         $err = $keys->err; $msg = $keys->msg;$lid = $keys->lid;
        }
      echo json_encode(array('err'=>$err,'msg'=>$msg,'lid'=>$lid));
		var_dump(array('err'=>$err,'msg'=>$msg,'lid'=>$lid));
	}

	public function gen_table($data){
		 $this->db->query('call ubs_populate_bill_prog_prc(?)',$data);
	}

	public function sync_used($data){
		$this->db->query('call ubs_used_charges_ins(?,?,?,?,?,?,@err,@msg)',$data);
	}

	public function sync_attempts($data){
		$this->db->query('call ubs_read_attempt_logs_ins(?,?,?,?,@err,@msg)',$data);
	}

	public function sync_getUser() {

		$sql = "SELECT au.user_name username , au.user_name password, au.user_id user_id FROM app_users au ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function sync_getZones(){
		$sql = "SELECT * FROM ar_zone1_values_v";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function sync_getZones2(){
		$sql = "SELECT * FROM ar_zone2_values_v  ";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function sync_used_in(){
		$sql = "SELECT * FROM `ubs_used_charges` WHERE reading_id IN (SELECT max(reading_id) from ubs_meter_readings group by account_id)";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function getCustomers(){
		//return $this-db->query()
	}

	// public function sync_cust() {
	// 	$sql = "SELECT ucl.account_id account_id
 //            ,ucl.account_number account_number
 //            ,ucl.name name
 //            ,ld.serial_number serial_number
 //            ,ucl.attention attention
 //            ,ucl.street_address street_address
 //            ,ucl.street_address2 street_address2
 //            ,ucl.city city
 //            ,ucl.meter_reading meter_reading
 //            ,ucl.reading_date reading_date 
 //            ,ucl.bill_balance bill_balance
 //            ,ucl.latest_due_date latest_due_date
 //            ,ucl.latest_pay_date latest_pay_date
 //            ,ucl.customer_type customer_type
 //            ,ucl.wo_code wo_code
 //            ,ucl.wo_date wo_date
 //            ,ucl.wo_last_reading wo_last_reading
 //            ,ucl.wo_new_reading wo_new_reading
 //            ,ucl.status status
 //            ,3m_average as average FROM ubs_cust_latest_readings_v ucl,get_latest_device ld";

	// 	$query = $this->db->query($sql); 
	// 	return $query->result();
	// }

	public function sync_cust($zone,$user) {
		$sql = "SELECT account_id
            , account_number
            , serial_number
            , name
            , list_id
            , attention
            , latitude
            , longitude
            , street_address
            , street_address2
            , city
            , meter_reading
            , reading_date
            , bill_balance
            , adv_pay
            ,latest_due_date
            ,latest_pay_date
            ,customer_type
            ,wo_code
            ,wo_date
            ,wo_last_reading
            ,wo_new_reading
            ,status
            ,3m_average as average
			,zone
			,book
			,meter_seq
			,reader_id
			,meter_size FROM ubs_acct_by_task_v WHERE zone = '".$zone."' AND reader_id = '".$user."' ";


		$query = $this->db->query($sql);
		return $query->result();
	}
    public function sync_chg($zone,$user) {
        //$sql = "SELECT * FROM ubs_acct_latest_chgs_v";

        $sql = "SELECT * FROM ubs_charges_by_tasks_v WHERE zone = '".$zone."' AND reader_id = '".$user."' ";
		$query = $this->db->query($sql);
		return $query->result();

        // $query = $this->db->get('ubs_acct_latest_chgs_v');
        // return $query->result();
    }

    public function sync_dischg() {
        //$sql = "SELECT * FROM ubs_acct_latest_chgs_v";
        $query = $this->db->get('ubs_discount_rates_v');
        return $query->result();
    }

	public function syncRates() {
		$sql = "SELECT * FROM ubs_rates_mobile_v";
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function sync_bills_mother($data){
		$devmark;
		if($data['sp_reason'] == 'DF'){
			$devmark = "Defective Meter";
		}else if($data['sp_reason'] == 'MIA'){
			$devmark = "Meter In-Accesable";
		}else{
			$devmark = " ";
		}
		$data3 = array(
			  ':p_account_id'    => $data['account_id']
			 ,':p_prev_read'     => $data['prev_read']
			 ,':p_prev_date'     => $data['prev_date']
			 ,':p_meter_reading' => $data['meter_reading']
			 ,':p_reading_date'  => $data['reading_date']
			 ,':p_consumed'      => $data['consumed']
			 ,':p_bill_date'     => $data['bill_date']
			 ,':p_bill_month'    => $data['bill_month']
			 ,':p_bill_year'     => $data['bill_year']
			 ,':p_read_amt'      => $data['read_amt']
			 ,':p_bill_bal'      => $data['bill_bal']
			 ,':p_total_due'     => $data['total_due']
			 ,':p_curchg'        => $data['curchg']
			 ,':p_due_date'      => $data['due_date']
			 ,':p_print_flag'    => $data['print_flag']
			 ,':p_print_by'      => $data['print_by']
			 ,':p_print_date'    => $data['print_date']
			 ,':p_sp_flag'       => $data['sp_flag']
			 ,':p_sp_readType'   => $data['sp_readType']
			 ,':p_sp_reason'     => $devmark
			 ,':p_max_rate'		 => $data['rates']
			 ,':p_remarks'		 => $data['remarks']
		);
		$this->db->query('call ubs_water_readings_ins_excmp(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,@a,@b,@c)', $data3);
		$result = $this->db->query('SELECT @a as status');
		$status;
		$inv;
		foreach($result->result() as $key){
			$status = $key->status;
		}
		return $result->result();
	
	}

	public function sync_bills($data) {
		$devmark;
		if($data['sp_reason'] == 'DF'){
			$devmark = "Defective Meter";
		}else if($data['sp_reason'] == 'MIA'){
			$devmark = "Meter In-Accesable";
		}else if($data['sp_reason'] == 'ACTREADS'){
			$devmark = "Actual Reading";
		}else{
			$devmark = " ";
		}
		$data3 = array(
			  ':p_account_id'    => $data['account_id']
			 ,':p_prev_read'     => $data['prev_read']
			 ,':p_prev_date'     => $data['prev_date']
			 ,':p_meter_reading' => $data['meter_reading']
			 ,':p_reading_date'  => $data['reading_date']
			 ,':p_consumed'      => $data['consumed']
			 ,':p_bill_date'     => $data['bill_date']
			 ,':p_bill_month'    => $data['bill_month']
			 ,':p_bill_year'     => $data['bill_year']
			 ,':p_read_amt'      => $data['read_amt']
			 ,':p_bill_bal'      => $data['bill_bal']
			 ,':p_total_due'     => $data['total_due']
			 ,':p_curchg'        => $data['curchg']
			 ,':p_adv_pay'		 => $data['adv_pay']
			 ,':p_due_date'      => $data['due_date']
			 ,':p_print_flag'    => $data['print_flag']
			 ,':p_print_by'      => $data['print_by']
			 ,':p_print_date'    => $data['print_date']
			 ,':p_sp_flag'       => $data['sp_flag']
			 ,':p_sp_readType'   => $data['sp_readType']
			 ,':p_sp_reason'     => $devmark
			 ,':p_max_rate'		 => $data['rates']
			 ,':p_remarks'		 => $data['remarks']
			 ,':p_findings'		 => $data['findings']
		);
		$this->db->query('call ubs_water_readings_ins(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,@a,@b,@c)', $data3);
		$result = $this->db->query('SELECT @a as status');
		$status;
		$inv;
		foreach($result->result() as $key){
			$status = $key->status;
		}
		return $result->result();
	}

	public function sync_water_readings($zone,$user) {
		//$q = $this->db->get('ubs_latest_readings_v');

		$sql = "SELECT * FROM ubs_mreads_by_tasks_v WHERE zone = '".$zone."' AND reader_id = '".$user."' ";
		$query = $this->db->query($sql);
		return $query->result();

		//return $q->result();
	}

	public function check($id){
		$q = $this->db->query("select * from ubs_invoice_lines WHERE invoice_id IN (SELECT invoice_id FROM ubs_invoices WHERE invoice_num = '".$id."')");
		var_dump($q->result());
		$this->db->where('bill_id',$id);
		$qq = $this->db->get('ubs_meter_readings');
		var_dump($qq->result());
	}

	public function getData(){
		$day = (date('m') < 10 ? '0'.date('m') : date('m'));
		$q = $this->db->query('SELECT umr.`account_id`,
			       umr.`bill_year`,
			       umr.`bill_month`,
			       umr.`total_due`,
			       umr.bill_bal,
			       umr.`due_date`,
			       umr.bill_date,
			       umr.reading_date,
			       umr.`prev_date`
			FROM ubs_meter_readings umr WHERE umr.bill_month = '.$day.' AND umr.bill_year = '.date('Y'));
		return $q->result();
	}
	
	public function get_query(){
      $q = $this->db->query('SELECT aa.`account_id`
        ,aa.`acct_num`
        ,aa.`acct_name`
      FROM ar_account aa');
      return $q->result();
    }
    
    public function get_pdf(){
      $q = $this->db->query('SELECT *
      FROM app_prog_pdf_outputs');
      return $q->result();
    }

    public function get_mother_meter_readings(){
    	$q = $this->db->get('ubs_mmeter_latest_readings_v');
		return $q->result();
    }

    public function get_mother_meters(){
    	$sql = "SELECT umm.*,umv.current,umv.last_read_month, umv.last_read_year, umv.last_read_date
    		  FROM ubs_mother_meter umm 
    		  	INNER JOIN ubs_mmeter_latest_readings_v umv ON umv.mmeter_id = umm.mmeter_id";
    	$q = $this->db->query($sql);
    	return $q->result();
    }

    public function save_reads($data){
        $this->db->query("call ubs_mother_meter_readings_ins(?,?,?,?,?,?,?,?,?,@err,@msg)",$data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
        return $que->result();
    }
	
}